package org.infodavid.common.persistence.impl;

import java.io.BufferedReader;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Arrays;

import javax.persistence.PersistenceException;

import org.apache.commons.lang.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.infodavid.common.persistence.IConnectionConfiguration;
import org.infodavid.common.persistence.IConnectionHelper;
import org.infodavid.common.persistence.SqlScriptHelper;

import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;
import com.mysql.jdbc.jdbc2.optional.MysqlXAConnection;
import com.mysql.jdbc.jdbc2.optional.MysqlXADataSource;

/**
 * The Class MySQLConnectionHelper.
 */
public class MySQLConnectionHelper implements IConnectionHelper {

  /** serialVersionUID. */
  private static final long serialVersionUID = 8140735779982552893L;

  /** The Constant DEFAULT_SCHEMA. */
  public static final String DEFAULT_SCHEMA = "test";

  /** The Constant DEFAULT_HOST. */
  public static final String DEFAULT_HOST = "127.0.0.1";

  /** The Constant LOGGER. */
  private static final Log LOGGER = LogFactory
      .getLog(MySQLConnectionHelper.class);

  /** The Constant TYPES. */
  public static final String[] TYPES = { "mysql", "mysql-5" };

  /** The Constant MAX_QUERIES_PER_HOUR_EXPRESSION. */
  private static final String MAX_QUERIES_PER_HOUR_EXPRESSION = "${max_queries_per_hour}";

  /** The Constant MAX_CONNECTIONS_PER_HOUR_EXPRESSION. */
  private static final String MAX_CONNECTIONS_PER_HOUR_EXPRESSION = "${max_connections_per_hour}";

  /** The Constant MAX_UPDATES_PER_HOUR_EXPRESSION. */
  private static final String MAX_UPDATES_PER_HOUR_EXPRESSION = "${max_updates_per_hour}";

  /** The Constant MAX_USER_CONNECTIONS_PER_HOUR_EXPRESSION. */
  private static final String MAX_USER_CONNECTIONS_PER_HOUR_EXPRESSION = "${max_user_connections_per_hour}";

  /** The Constant MAX_QUERIES_PER_HOUR_PROPERTY. */
  private static final String MAX_QUERIES_PER_HOUR_PROPERTY = "max_queries_per_hour";

  /** The Constant MAX_CONNECTIONS_PER_HOUR_PROPERTY. */
  private static final String MAX_CONNECTIONS_PER_HOUR_PROPERTY = "max_connections_per_hour";

  /** The Constant MAX_UPDATES_PER_HOUR_PROPERTY. */
  private static final String MAX_UPDATES_PER_HOUR_PROPERTY = "max_updates_per_hour";

  /** The Constant MAX_USER_CONNECTIONS_PER_HOUR_PROPERTY. */
  private static final String MAX_USER_CONNECTIONS_PER_HOUR_PROPERTY = "max_user_connections_per_hour";

  /** The Constant GET_SCHEMA_QRY. */
  private static final String GET_SCHEMA_QRY = "SELECT schema_name FROM INFORMATION_SCHEMA.SCHEMATA WHERE schema_name = ?";

  /** The Constant CONNECTION_STRING_PREFIX. */
  public static final String CONNECTION_STRING_PREFIX = "jdbc:mysql://";

  /** The Constant CONNECTION_STRING_SUFFFIX. */
  public static final String CONNECTION_STRING_SUFIX = "?autoReconnectForPools=true"
      + "&roundRobinLoadBalance=true&loadBalanceEnableJMX=true";

  /**
   * The Constructor.
   */
  public MySQLConnectionHelper() {
    super();
  }

  /**
   * See super class or interface. (non-Javadoc)
   *
   * @see org.infodavid.common.persistence.IConnectionHelper#hasSchema(java.sql.Connection,
   *      java.lang.String)
   */
  @Override
  public boolean hasSchema(final Connection connection, final String name)
      throws PersistenceException {
    PreparedStatement statement = null;
    ResultSet resultSet = null;
    boolean result = false;

    if (connection == null) {
      throw new PersistenceException("Given JDBC connection is not valid");
    }

    if (name == null) {
      throw new PersistenceException("Given schema name is not valid");
    }

    if (LOGGER.isDebugEnabled()) {
      LOGGER.debug("Checking if schema exists: " + name);
    }

    try {
      statement = connection.prepareStatement(GET_SCHEMA_QRY);

      statement.setString(1, name);
      resultSet = statement.executeQuery();

      result = resultSet.first();
    } catch (final SQLException e) {
      throw new PersistenceException(
          "An errors occurs while executing SQL query", e);
    } finally {
      PersistenceUtil.release(resultSet);
      PersistenceUtil.release(statement);
    }

    if (LOGGER.isDebugEnabled()) {
      LOGGER.debug("Schema exists: " + result);
    }

    return result;
  }

  /**
   * See super class or interface. (non-Javadoc)
   *
   * @see org.infodavid.common.persistence.IConnectionHelper#createSchema(java.sql.Connection,
   *      com.org.infodavid.common.persistence.IConnectionConfiguration)
   */
  @Override
  public void createSchema(final Connection connection,
      final IConnectionConfiguration conf) throws PersistenceException {
    validate(conf);

    if (connection == null) {
      throw new PersistenceException("Given JDBC connection is not valid");
    }

    if (LOGGER.isDebugEnabled()) {
      LOGGER.debug("Creating schema: " + conf);
    }

    final SqlScriptHelper helper = SqlScriptHelper.getInstance();
    final StringBuilder buffer = new StringBuilder();
    BufferedReader reader = null;
    InputStream in;
    String[] lines;
    String line;

    try {
      in = getClass().getResourceAsStream("/mysql_create_schema.sql");
      reader = new BufferedReader(new InputStreamReader(in));

      while ((line = reader.readLine()) != null) {
        buffer.append(line).append('\n');
      }

      lines = helper.parse(configure(buffer, conf));
      helper.execute(connection, lines);
    } catch (final Exception e) {
      throw new PersistenceException(
          "An error occurs while creating schema: " + conf, e);
    } finally {
      if (reader != null) {
        try {
          reader.close();
        } catch (final Exception e) {
          LOGGER.debug("An error occurs while closing stream", e);
        }
      }
    }
  }

  /**
   * See super class or interface. (non-Javadoc)
   *
   * @see org.infodavid.common.persistence.IConnectionHelper#createUser(java.sql.Connection,
   *      java.lang.String, java.lang.String)
   */
  @Override
  public void createUser(final Connection connection, final String username,
      final String password) throws PersistenceException {
    if (connection == null) {
      throw new PersistenceException("Given JDBC connection is not valid");
    }

    if (LOGGER.isDebugEnabled()) {
      LOGGER.debug("Creating user: " + username + ", password:"
          + (password == null ? "" : password.replaceAll("\\.", "#")));
    }

    final IConnectionConfiguration conf = new ConnectionConfigurationImpl();
    final SqlScriptHelper helper = SqlScriptHelper.getInstance();
    final StringBuilder buffer = new StringBuilder();
    BufferedReader reader = null;
    InputStream in;
    String[] lines;
    String line;

    try {
      in = getClass().getResourceAsStream("/mysql_add_user.sql");
      reader = new BufferedReader(new InputStreamReader(in));

      while ((line = reader.readLine()) != null) {
        buffer.append(line).append('\n');
      }

      conf.setUser(username);
      conf.setPassword(password);
      lines = helper.parse(configure(buffer, conf));
      helper.execute(connection, lines);
    } catch (final Exception e) {
      throw new PersistenceException(
          "An error occurs while adding user: " + username, e);
    } finally {
      if (reader != null) {
        try {
          reader.close();
        } catch (final Exception e) {
          LOGGER.debug("An error occurs while closing stream", e);
        }
      }
    }
  }

  /**
   * See super class or interface. (non-Javadoc)
   *
   * @see org.infodavid.common.persistence.IConnectionHelper#deleteUser(java.sql.Connection,
   *      java.lang.String)
   */
  @Override
  public void deleteUser(final Connection connection, final String username)
      throws PersistenceException {
    if (connection == null) {
      throw new PersistenceException("Given JDBC connection is not valid");
    }

    if (LOGGER.isDebugEnabled()) {
      LOGGER.debug("Deleting user: " + username);
    }

    final IConnectionConfiguration conf = new ConnectionConfigurationImpl();
    final SqlScriptHelper helper = SqlScriptHelper.getInstance();
    final StringBuilder buffer = new StringBuilder();
    BufferedReader reader = null;
    InputStream in;
    String[] lines;
    String line;

    try {
      in = getClass().getResourceAsStream("/mysql_delete_user.sql");
      reader = new BufferedReader(new InputStreamReader(in));

      while ((line = reader.readLine()) != null) {
        buffer.append(line).append('\n');
      }

      conf.setUser(username);
      lines = helper.parse(configure(buffer, conf));
      helper.execute(connection, lines);
    } catch (final Exception e) {
      throw new PersistenceException(
          "An error occurs while deleting user: " + username, e);
    } finally {
      if (reader != null) {
        try {
          reader.close();
        } catch (final Exception e) {
          LOGGER.debug("An error occurs while closing stream", e);
        }
      }
    }
  }

  /**
   * Configure.
   *
   * @param buffer
   *            the buffer
   * @param configuration
   *            the configuration
   * @return the string
   */
  private String configure(final StringBuilder buffer,
      final IConnectionConfiguration configuration) {
    String content = buffer.toString();
    String value;

    value = configuration.getStdConnectionString();

    if (StringUtils.isNotEmpty(value)) {
      LOGGER.debug("Parsing connection string");

      if (value.length() > 15) {
        // like jdbc:mysql://host:3306/schema
        value = value.substring(13);
        int position = value.indexOf(':');

        if (position >= 0) {
          final String host = value.substring(0, position);

          LOGGER.debug("Found host: " + host);

          position = value.indexOf('/');

          if (position >= 0) {

            final String schema = value.substring(position + 1,
                value.length());

            LOGGER.debug("Found schema: " + schema);

            if (host == null || host.trim().length() > 0) {
              content = content.replace(HOST_EXPRESSION, host);
            }

            if (schema == null || schema.trim().length() > 0) {
              content = content
                  .replace(SCHEMA_EXPRESSION, schema);
            }
          }
        }
      }
    }

    value = configuration.getUser();

    if (StringUtils.isNotEmpty(value)) {
      content = content.replace(USER_EXPRESSION, value);
    }

    value = configuration.getPassword();

    if (StringUtils.isNotEmpty(value)) {
      content = content.replace(PASSWORD_EXPRESSION, value);
    }

    value = configuration.getHost();

    if (StringUtils.isNotEmpty(value)) {
      content = content.replace(HOST_EXPRESSION, value);
    }

    value = configuration.getSchema();

    if (StringUtils.isNotEmpty(value)) {
      content = content.replace(SCHEMA_EXPRESSION, value);
    }

    value = configuration.getProperty(MAX_QUERIES_PER_HOUR_PROPERTY);

    if (StringUtils.isNotEmpty(value)) {
      content = content.replace(MAX_QUERIES_PER_HOUR_EXPRESSION, value);
    } else {
      content = content.replace(MAX_QUERIES_PER_HOUR_EXPRESSION, "0");
    }

    value = configuration.getProperty(MAX_CONNECTIONS_PER_HOUR_PROPERTY);

    if (StringUtils.isNotEmpty(value)) {
      content = content.replace(MAX_CONNECTIONS_PER_HOUR_EXPRESSION,
          value);
    } else {
      content = content.replace(MAX_CONNECTIONS_PER_HOUR_EXPRESSION, "0");
    }

    value = configuration.getProperty(MAX_UPDATES_PER_HOUR_PROPERTY);

    if (StringUtils.isNotEmpty(value)) {
      content = content.replace(MAX_UPDATES_PER_HOUR_EXPRESSION, value);
    } else {
      content = content.replace(MAX_UPDATES_PER_HOUR_EXPRESSION, "0");
    }

    value = configuration
        .getProperty(MAX_USER_CONNECTIONS_PER_HOUR_PROPERTY);

    if (StringUtils.isNotEmpty(value)) {
      content = content.replace(MAX_USER_CONNECTIONS_PER_HOUR_EXPRESSION,
          value);
    } else {
      content = content.replace(MAX_USER_CONNECTIONS_PER_HOUR_EXPRESSION,
          "0");
    }

    return content;
  }

  /**
   * Validate.
   *
   * @param conf
   *            the configuration
   * @throws PersistenceException
   *             the persistence exception
   */
  private void validate(final IConnectionConfiguration conf)
      throws PersistenceException {
    if (conf.getType() == null || conf.getType().trim().length() == 0) {
      throw new PersistenceException(
          "This configuration has no database type: " + conf);
    }

    if (Arrays.binarySearch(getTypes(), conf.getType()) < 0) {
      throw new PersistenceException(
          "This helper can't use the given database type: "
              + conf.getType());
    }
  }

  /**
   * See super class or interface. (non-Javadoc)
   *
   * @see org.infodavid.common.persistence.IConnectionHelper#openConnection(com.org.infodavid.common.persistence.IConnectionConfiguration)
   */
  @Override
  public Connection openConnection(final IConnectionConfiguration conf)
      throws PersistenceException {
    Connection connection = null;

    validate(conf);

    try {
      Class.forName("com.mysql.jdbc.Driver").newInstance();

      String connectionString = conf.getStdConnectionString();

      if (connectionString == null
          || connectionString.trim().length() == 0) {
        connectionString = getConnectionString(conf);
      }

      final MysqlDataSource ds = openDataSource(conf);
      connection = ds.getConnection();

      if (!connection.isClosed()) {
        LOGGER.debug("Successfully connected to " + conf);
      }
    } catch (final SQLException e) {
      throw new PersistenceException(
          "An errors occurs while opening connection", e);
    } catch (final InstantiationException e) {
      throw new PersistenceException(
          "An errors occurs while retrieving the JDBC driver", e);
    } catch (final IllegalAccessException e) {
      throw new PersistenceException(
          "An errors occurs while retrieving the JDBC driver", e);
    } catch (final ClassNotFoundException e) {
      throw new PersistenceException(
          "An errors occurs while retrieving the JDBC driver", e);
    }

    return connection;
  }

  /**
   * See super class or interface. (non-Javadoc)
   *
   * @see org.infodavid.common.persistence.IConnectionHelper#openXaConnection(com.org.infodavid.common.persistence.IConnectionConfiguration)
   */
  @Override
  public MysqlXAConnection openXaConnection(
      final IConnectionConfiguration conf) throws PersistenceException {
    MysqlXAConnection connection = null;

    validate(conf);

    try {
      Class.forName("com.mysql.jdbc.Driver").newInstance();

      String connectionString = conf.getStdConnectionString();

      if (connectionString == null
          || connectionString.trim().length() == 0) {
        connectionString = getConnectionString(conf);
      }

      final MysqlDataSource ds = openDataSource(conf);
      connection = new MysqlXAConnection(
          (com.mysql.jdbc.ConnectionImpl) ds.getConnection(), false);

      LOGGER.debug("Successfully connected to " + conf);
    } catch (final SQLException e) {
      throw new PersistenceException(
          "An errors occurs while opening connection", e);
    } catch (final InstantiationException e) {
      throw new PersistenceException(
          "An errors occurs while retrieving the JDBC driver", e);
    } catch (final IllegalAccessException e) {
      throw new PersistenceException(
          "An errors occurs while retrieving the JDBC driver", e);
    } catch (final ClassNotFoundException e) {
      throw new PersistenceException(
          "An errors occurs while retrieving the JDBC driver", e);
    }

    return connection;
  }

  /**
   * See super class or interface. (non-Javadoc)
   *
   * @see org.infodavid.common.persistence.IConnectionHelper#openDataSource(com.org.infodavid.common.persistence.IConnectionConfiguration)
   */
  @Override
  public MysqlDataSource openDataSource(final IConnectionConfiguration conf)
      throws PersistenceException {
    MysqlDataSource dataSource = null;

    validate(conf);

    try {
      Class.forName("com.mysql.jdbc.Driver").newInstance();

      dataSource = new MysqlDataSource();

      dataSource.setUser(conf.getUser());
      dataSource.setPassword(conf.getPassword());
      dataSource.setServerName(conf.getHost());
      dataSource.setDatabaseName(conf.getSchema());

      if (conf.getTcpPort() != null) {
        dataSource.setPort(conf.getTcpPort().intValue());
      }

      LOGGER.debug("Successfully connected to " + conf);
    } catch (final InstantiationException e) {
      throw new PersistenceException(
          "An errors occurs while retrieving the JDBC driver", e);
    } catch (final IllegalAccessException e) {
      throw new PersistenceException(
          "An errors occurs while retrieving the JDBC driver", e);
    } catch (final ClassNotFoundException e) {
      throw new PersistenceException(
          "An errors occurs while retrieving the JDBC driver", e);
    }

    return dataSource;
  }

  /**
   * See super class or interface. (non-Javadoc)
   *
   * @see org.infodavid.common.persistence.IConnectionHelper#openXaDataSource(com.org.infodavid.common.persistence.IConnectionConfiguration)
   */
  @Override
  public MysqlXADataSource openXaDataSource(
      final IConnectionConfiguration conf) throws PersistenceException {
    MysqlXADataSource dataSource = null;

    validate(conf);

    try {
      Class.forName("com.mysql.jdbc.Driver").newInstance();

      String connectionString = conf.getStdConnectionString();

      if (connectionString == null
          || connectionString.trim().length() == 0) {
        connectionString = getConnectionString(conf);
      }

      dataSource = new MysqlXADataSource();

      dataSource.setUser(conf.getUser());
      dataSource.setPassword(conf.getPassword());
      dataSource.setServerName(conf.getHost());
      dataSource.setDatabaseName(conf.getSchema());

      if (conf.getTcpPort() != null) {
        dataSource.setPort(conf.getTcpPort().intValue());
      }

      LOGGER.debug("Successfully connected to " + conf);
    } catch (final InstantiationException e) {
      throw new PersistenceException(
          "An errors occurs while retrieving the JDBC driver", e);
    } catch (final IllegalAccessException e) {
      throw new PersistenceException(
          "An errors occurs while retrieving the JDBC driver", e);
    } catch (final ClassNotFoundException e) {
      throw new PersistenceException(
          "An errors occurs while retrieving the JDBC driver", e);
    }

    return dataSource;
  }

  /**
   * See super class or interface. (non-Javadoc)
   *
   * @see org.infodavid.common.persistence.IConnectionHelper#getConnectionString(com.org.infodavid.common.persistence.IConnectionConfiguration)
   */
  @Override
  public String getConnectionString(final IConnectionConfiguration conf)
      throws PersistenceException {
    if (LOGGER.isDebugEnabled()) {
      LOGGER.debug("Trying to build a connection string for configuration: "
          + conf);
    }

    validate(conf);

    final StringBuffer buffer = new StringBuffer(128);

    buffer.append(CONNECTION_STRING_PREFIX);

    String strValue = conf.getHost();

    if (StringUtils.isEmpty(strValue)) {
      strValue = DEFAULT_HOST;
    }

    buffer.append(strValue);

    final Number numValue = conf.getTcpPort();

    if (!(numValue == null || numValue.intValue() < 0)) {
      buffer.append(':');
      buffer.append(numValue.intValue());
    }

    buffer.append('/');

    strValue = conf.getSchema();

    if (StringUtils.isEmpty(strValue)) {
      strValue = conf.getName();
    }

    if (StringUtils.isEmpty(strValue)) {
      strValue = conf.getUser();
    }

    if (StringUtils.isEmpty(strValue)) {
      strValue = DEFAULT_SCHEMA;
    }

    buffer.append(strValue);
    buffer.append(CONNECTION_STRING_SUFIX);

    if (LOGGER.isDebugEnabled()) {
      LOGGER.debug("Built connection string: " + buffer.toString());
    }

    return buffer.toString();
  }

  /**
   * See super class or interface. (non-Javadoc)
   *
   * @see org.infodavid.common.persistence.IConnectionHelper#getTypes()
   */
  @Override
  public String[] getTypes() {
    return TYPES;
  }
}
